package nbcp.myoql.code.generator.db.sql

import nbcp.base.extend.Important
import nbcp.base.extend.basicSame
import nbcp.myoql.code.generator.db.mysql.model.TableColumnMetaData
import nbcp.myoql.code.generator.db.mysql.model.TableIndexMetaData
import nbcp.myoql.code.generator.db.mysql.model.TableMetaData
import nbcp.myoql.db.db
import nbcp.myoql.db.sql.enums.DbType
import org.slf4j.LoggerFactory

class PostgreImpl :ISqlMetadata {
    companion object {
        private val logger = LoggerFactory.getLogger(javaClass.declaringClass)
    }



    override fun getDbType(dataType: String): DbType {
        if (dataType basicSame "varchar"
            || dataType basicSame "char"
            || dataType basicSame "nvarchar"
            || dataType basicSame "nchar"
            || dataType basicSame "uuid"
        ) {
            return DbType.STRING
        } else if (dataType basicSame "text"
            || dataType basicSame "mediumtext"
            || dataType basicSame "longtext"
        ) {
            return DbType.TEXT
        } else if (dataType basicSame "enum") {
            return DbType.ENUM
        } else if (dataType basicSame "json") {
            return DbType.JSON
        } else if (dataType basicSame "int" || dataType basicSame "integer") {
            return DbType.INT
        } else if (dataType basicSame "bit" || dataType basicSame "boolean") {
            return DbType.BOOLEAN
        } else if (dataType basicSame "datetime" ||
            dataType basicSame "timestamp"
        ) {
            return DbType.DATE_TIME
        } else if (dataType basicSame "date") {
            return DbType.DATE
        } else if (dataType basicSame "float" || dataType basicSame "real") {
            return DbType.FLOAT
        } else if (dataType basicSame "double" || dataType basicSame "numeric") {
            return DbType.DOUBLE
        } else if (dataType basicSame "long") {
            return DbType.LONG
        } else if (dataType basicSame "tinyint" || dataType basicSame "smallint") {
            return DbType.BYTE
        } else if (dataType basicSame "bigint") {
            return DbType.LONG
        } else if (dataType basicSame "decimal") {
            logger.Important("warning sql data type: ${dataType}")
            return DbType.DOUBLE
        }

        return DbType.TEXT
    }

    override fun getTables(): List<TableMetaData> {
        return db.sql.rawQuery(
            """
SELECT t.table_name AS  "tableName", 
obj_description(c.oid) AS "tableComment"
FROM "information_schema"."tables" AS t 
LEFT JOIN "pg_namespace" n ON t.table_schema = n.nspname 
LEFT JOIN "pg_class" c ON n.oid = c.relnamespace AND c.relname=t.table_name
WHERE t."table_schema"='public'
"""
        ).toList(TableMetaData::class.java)
    }

    override fun getColumns(): List<TableColumnMetaData> {
        return db.sql.rawQuery(
            """
SELECT
    pc.relname AS "tableName",
    pa.attname AS "columnName",
    pt.typname AS  "columnSqlType",
    ( CASE WHEN pa.attlen > 0 THEN pa.attlen ELSE pa.atttypmod - 4 END ) AS "charLength",
   
    (    CASE WHEN 
	 			( SELECT COUNT(*) FROM pg_constraint WHERE conrelid = pa.attrelid AND conkey[1]= attnum AND contype = 'p' ) > 0 
	 		THEN TRUE 
			 ELSE FALSE 
        END 
		  ) AS "isPrimary",
 pd.description AS "columnComment" ,
 CASE  when pc_rel.relname  IS NULL THEN FALSE  ELSE true END "isAutoIncrement" 
FROM pg_class pc
JOIN "information_schema"."tables" AS t  ON (pc.relname = t.table_name AND t."table_schema"='public')
JOIN  pg_attribute pa ON ( pc.oid = pa.attrelid AND pa.attstattarget != 0 ) 
JOIN  pg_type pt ON ( pt.oid = pa.atttypid ) 
left join pg_description pd ON ( pd.objoid = pa.attrelid  AND pd.objsubid = pa.attnum  ) 
LEFT JOIN pg_class pc_rel ON ( concat( pc.relname , '_' , pa.attname , '_seq' ) = pc_rel.relname )
ORDER BY pc.relname ASC,  pa.attnum ASC
"""
        ).toList(TableColumnMetaData::class.java)
    }

    override fun getIndexes(): List<TableIndexMetaData> {
        return db.sql.rawQuery(
            """
SELECT t.relname AS "tableName",  ix.indexrelid "indexName", 
     array_to_string(array_agg(a.attname), ', ') AS "columnName",
     COUNT(1) AS "seqInIndex" 
FROM
    pg_class t 
 join   pg_class i  ON ( t.oid = i.oid) 
 join   pg_index ix ON ( t.oid = ix.indrelid AND ix.indisunique =  true) 
 join   pg_attribute a ON (    a.attrelid = t.oid )
WHERE   t.relname IN ( SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public' ) 
and a.attnum = ANY(ix.indkey) AND t.relkind = 'r' 
AND ix.indisprimary = false
 GROUP BY
    t.relname,
    ix.indexrelid
HAVING COUNT(1) = 1
"""
        )
            .toList(TableIndexMetaData::class.java)
    }
}